using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;

namespace Forecast.Database
{
    public class Load
    {
        #region Variables

        private string _connectionString;

        #endregion
        #region Constructors

        // Forces use of overload
        private Load() { }

        public Load(string databaseConnectionString)
        {
            _connectionString = databaseConnectionString;
        }

        #endregion
        #region Interface

        #region Get single object

        public Holding GetHolding(int holdingId)
        {
            Holding holding = new Holding();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Holding.TABLE_NAME + "] where [" + Holding.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Holding.ColumnName.ID.ToString(), OleDbType.Integer).Value = holdingId;
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        holding.Id = (Int32)reader[Holding.ColumnName.ID.ToString()];
                        holding.NumPredictions = (Int32)reader[Holding.ColumnName.Num_predictions.ToString()];
                        holding.PlayerId = (Int32)reader[Holding.ColumnName.Player_id.ToString()];
                        holding.PredictionId = (Int32)reader[Holding.ColumnName.Prediction_id.ToString()];
                    }
                }
            }
            return holding;
        }

        public Holding GetHolding(Player player, Prediction prediction)
        {
            Holding holding = new Holding();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Holding.TABLE_NAME + "] where [" + Holding.ColumnName.Player_id.ToString() + "] = ? and [" + Holding.ColumnName.Prediction_id.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Holding.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = player.Id;
                command.Parameters.Add(Holding.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = prediction.Id;
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        holding.Id = (Int32)reader[Holding.ColumnName.ID.ToString()];
                        holding.NumPredictions = (Int32)reader[Holding.ColumnName.Num_predictions.ToString()];
                        holding.PlayerId = (Int32)reader[Holding.ColumnName.Player_id.ToString()];
                        holding.PredictionId = (Int32)reader[Holding.ColumnName.Prediction_id.ToString()];
                    }
                }
            }
            return holding;
        }

        public Medal GetMedal(int medalId)
        {
            Medal medal = new Medal();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Medal.TABLE_NAME + "] where [" + NewsItem.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Medal.ColumnName.ID.ToString(), OleDbType.Integer).Value = medalId;
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        medal.Id = (Int32)reader[Medal.ColumnName.ID.ToString()];
                        medal.Name = (String)reader[Medal.ColumnName.Medal.ToString()];
                        medal.Description = (String)reader[Medal.ColumnName.Description.ToString()];
                    }
                }
            }
            return medal;
        }

        public NewsItem GetNewsItem(int newsItemId)
        {
            NewsItem newsItem = new NewsItem();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + NewsItem.TABLE_NAME + "] where [" + NewsItem.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(NewsItem.ColumnName.ID.ToString(), OleDbType.Integer).Value = newsItemId;
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        newsItem.Id = (Int32)reader[NewsItem.ColumnName.ID.ToString()];
                        newsItem.Message = (String)reader[NewsItem.ColumnName.News_message.ToString()];
                        newsItem.NewsDate = (DateTime)reader[NewsItem.ColumnName.News_date.ToString()];
                        if (reader[NewsItem.ColumnName.Player_id.ToString()] != DBNull.Value)
                            newsItem.PlayerId = (Int32)reader[NewsItem.ColumnName.Player_id.ToString()];
                        if (reader[NewsItem.ColumnName.Prediction_id.ToString()] != DBNull.Value)
                            newsItem.PredictionId = (Int32)reader[NewsItem.ColumnName.Prediction_id.ToString()];
                        newsItem.Title = (String)reader[NewsItem.ColumnName.Title.ToString()];
                        newsItem.ShouldBeShownInMainNews = (Boolean)reader[NewsItem.ColumnName.Should_be_shown_in_main_news.ToString()];
                    }
                }
            }
            return newsItem;
        }

        public Offer GetOffer(int offerId)
        {
            Offer offer = new Offer();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Offer.TABLE_NAME + "] where [" + Offer.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Offer.ColumnName.ID.ToString(), OleDbType.Integer).Value = offerId;
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader[Offer.ColumnName.Acceptor.ToString()] != DBNull.Value)
                            offer.AcceptorId = (Int32)reader[Offer.ColumnName.Acceptor.ToString()];
                        offer.EndDate = (DateTime)reader[Offer.ColumnName.End_date.ToString()];
                        offer.Id = (Int32)reader[Offer.ColumnName.ID.ToString()];
                        offer.IsAccepted = (bool)reader[Offer.ColumnName.Is_accepted.ToString()];
                        offer.IsActive = (bool)reader[Offer.ColumnName.Is_active.ToString()];
                        offer.IsBuy = (bool)reader[Offer.ColumnName.Is_buy.ToString()];
                        offer.NumPredictions = (Int32)reader[Offer.ColumnName.Num_predictions.ToString()];
                        offer.PlayerId = (Int32)reader[Offer.ColumnName.Player_id.ToString()];
                        offer.PredictionId = (Int32)reader[Offer.ColumnName.Prediction_id.ToString()];
                        offer.Price = (Int32)reader[Offer.ColumnName.Price.ToString()];
                    }
                }
            }
            return offer;
        }

        public Player GetPlayer(int playerId)
        {
            Player player = new Player();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Player.TABLE_NAME + "] where [" + Player.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Player.ColumnName.ID.ToString(), OleDbType.Integer).Value = playerId;
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        player.Cash = (Int32)reader[Player.ColumnName.Cash.ToString()];
                        player.Id = (Int32)reader[Player.ColumnName.ID.ToString()];
                        player.UserName = (String)reader[Player.ColumnName.User_name.ToString()];
                    }
                }
            }
            return player;
        }

        public Prediction GetPrediction(int predictionId)
        {
            Prediction prediction = new Prediction();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Prediction.TABLE_NAME + "] where [" + Prediction.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Prediction.ColumnName.ID.ToString(), OleDbType.Integer).Value = predictionId;
                
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader[Prediction.ColumnName.End_date.ToString()] != DBNull.Value)
                            prediction.EndDate = (DateTime)reader[Prediction.ColumnName.End_date.ToString()];
                        prediction.HasSucceeded = (bool)reader[Prediction.ColumnName.Has_succeeded.ToString()];
                        prediction.Id = (Int32)reader[Prediction.ColumnName.ID.ToString()];
                        prediction.IsActive = (bool)reader[Prediction.ColumnName.Is_active.ToString()];
                        prediction.IsClosed = (bool)reader[Prediction.ColumnName.Is_closed.ToString()];
                        prediction.LastChange = (decimal)reader[Prediction.ColumnName.Last_change.ToString()];
                        prediction.LastValue = (Int32)reader[Prediction.ColumnName.Last_value.ToString()];
                        prediction.LongDescription = (String)reader[Prediction.ColumnName.Long_description.ToString()];
                        prediction.ShortDescription = (String)reader[Prediction.ColumnName.Short_description.ToString()];
                        prediction.Ticker = (String)reader[Prediction.ColumnName.Ticker.ToString()];
                    }
                }                
            }
            return prediction;
        }

        public PredictionResult GetPredictionResult(int predictionResultId)
        {
            PredictionResult predictionResult = new PredictionResult();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + PredictionResult.TABLE_NAME + "] where [" + PredictionResult.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Prediction.ColumnName.ID.ToString(), OleDbType.Integer).Value = predictionResultId;

                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        predictionResult.CashEarned = (Int32)reader[PredictionResult.ColumnName.Cash_earned.ToString()];
                        predictionResult.Id = (Int32)reader[PredictionResult.ColumnName.ID.ToString()];
                        predictionResult.MedalId = (Int32)reader[PredictionResult.ColumnName.Medal_id.ToString()];
                        predictionResult.NumTrades = (Int32)reader[PredictionResult.ColumnName.Num_trades.ToString()];
                        predictionResult.PlayerId = (Int32)reader[PredictionResult.ColumnName.Player_id.ToString()];
                        predictionResult.PredictionId = (Int32)reader[PredictionResult.ColumnName.Prediction_id.ToString()];                        
                    }
                }
            }
            return predictionResult;
        }


        #endregion
        #region Get collection

        public IList<NewsItem> GetAllNewsItems()
        {
            IList<NewsItem> allNewsItems = new List<NewsItem>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + NewsItem.TABLE_NAME + "]";
                sql += " order by [" + NewsItem.ColumnName.News_date + "] DESC";
                OleDbCommand command = new OleDbCommand(sql, connection);
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        NewsItem newsItem = new NewsItem();
                        
                        newsItem.Id = (Int32)reader[NewsItem.ColumnName.ID.ToString()];
                        newsItem.Message = (String)reader[NewsItem.ColumnName.News_message.ToString()];
                        newsItem.NewsDate = (DateTime)reader[NewsItem.ColumnName.News_date.ToString()];
                        if (reader[NewsItem.ColumnName.Player_id.ToString()] != DBNull.Value)
                            newsItem.PlayerId = (Int32)reader[NewsItem.ColumnName.Player_id.ToString()];
                        if (reader[NewsItem.ColumnName.Prediction_id.ToString()] != DBNull.Value)
                            newsItem.PredictionId = (Int32)reader[NewsItem.ColumnName.Prediction_id.ToString()];
                        newsItem.Title = (String)reader[NewsItem.ColumnName.Title.ToString()];
                        newsItem.ShouldBeShownInMainNews = (Boolean)reader[NewsItem.ColumnName.Should_be_shown_in_main_news.ToString()];

                        allNewsItems.Add(newsItem);
                    }
                }
            }
            return allNewsItems;
        }

        public IList<Offer> GetActiveOffers(Prediction prediction)
        {
            IList<Offer> offers = new List<Offer>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Offer.TABLE_NAME + "] where [" + Offer.ColumnName.Prediction_id.ToString() + "] = ? and [" + Offer.ColumnName.Is_active.ToString() + "] = ?";
                sql += " order by [" + Offer.ColumnName.Price.ToString() + "] DESC";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Offer.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = prediction.Id;
                command.Parameters.Add(Offer.ColumnName.Is_active.ToString(), OleDbType.Boolean).Value = true;

                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Offer offer = new Offer();
                        if (reader[Offer.ColumnName.Acceptor.ToString()] != DBNull.Value)
                            offer.AcceptorId = (Int32)reader[Offer.ColumnName.Acceptor.ToString()];
                        offer.EndDate = (DateTime)reader[Offer.ColumnName.End_date.ToString()];
                        offer.Id = (Int32)reader[Offer.ColumnName.ID.ToString()];
                        offer.IsAccepted = (bool)reader[Offer.ColumnName.Is_accepted.ToString()];
                        offer.IsActive = (bool)reader[Offer.ColumnName.Is_active.ToString()];
                        offer.IsBuy = (bool)reader[Offer.ColumnName.Is_buy.ToString()];
                        offer.NumPredictions = (Int32)reader[Offer.ColumnName.Num_predictions.ToString()];
                        offer.PlayerId = (Int32)reader[Offer.ColumnName.Player_id.ToString()];
                        offer.PredictionId = (Int32)reader[Offer.ColumnName.Prediction_id.ToString()];
                        offer.Price = (Int32)reader[Offer.ColumnName.Price.ToString()];
                        offers.Add(offer);
                    }
                }
            }
            return offers;
        }

        public IList<Offer> GetAllActiveOffers()
        {
            IList<Offer> offers = new List<Offer>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Offer.TABLE_NAME + "] where [" + Offer.ColumnName.Is_active.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Offer.ColumnName.Is_active.ToString(), OleDbType.Boolean).Value = true;

                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Offer offer = new Offer();
                        if (reader[Offer.ColumnName.Acceptor.ToString()] != DBNull.Value)
                            offer.AcceptorId = (Int32)reader[Offer.ColumnName.Acceptor.ToString()];
                        offer.EndDate = (DateTime)reader[Offer.ColumnName.End_date.ToString()];
                        offer.Id = (Int32)reader[Offer.ColumnName.ID.ToString()];
                        offer.IsAccepted = (bool)reader[Offer.ColumnName.Is_accepted.ToString()];
                        offer.IsActive = (bool)reader[Offer.ColumnName.Is_active.ToString()];
                        offer.IsBuy = (bool)reader[Offer.ColumnName.Is_buy.ToString()];
                        offer.NumPredictions = (Int32)reader[Offer.ColumnName.Num_predictions.ToString()];
                        offer.PlayerId = (Int32)reader[Offer.ColumnName.Player_id.ToString()];
                        offer.PredictionId = (Int32)reader[Offer.ColumnName.Prediction_id.ToString()];
                        offer.Price = (Int32)reader[Offer.ColumnName.Price.ToString()];
                        offers.Add(offer);
                    }
                }
            }
            return offers;
        }

        public IList<Offer> GetAllOffers()
        {
            IList<Offer> offers = new List<Offer>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Offer.TABLE_NAME + "]";
                sql += " order by [" + Offer.ColumnName.End_date.ToString() + "] ASC";
                OleDbCommand command = new OleDbCommand(sql, connection);
                
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Offer offer = new Offer();
                        if (reader[Offer.ColumnName.Acceptor.ToString()] != DBNull.Value)
                            offer.AcceptorId = (Int32)reader[Offer.ColumnName.Acceptor.ToString()];
                        offer.EndDate = (DateTime)reader[Offer.ColumnName.End_date.ToString()];
                        offer.Id = (Int32)reader[Offer.ColumnName.ID.ToString()];
                        offer.IsAccepted = (bool)reader[Offer.ColumnName.Is_accepted.ToString()];
                        offer.IsActive = (bool)reader[Offer.ColumnName.Is_active.ToString()];
                        offer.IsBuy = (bool)reader[Offer.ColumnName.Is_buy.ToString()];
                        offer.NumPredictions = (Int32)reader[Offer.ColumnName.Num_predictions.ToString()];
                        offer.PlayerId = (Int32)reader[Offer.ColumnName.Player_id.ToString()];
                        offer.PredictionId = (Int32)reader[Offer.ColumnName.Prediction_id.ToString()];
                        offer.Price = (Int32)reader[Offer.ColumnName.Price.ToString()];
                        offers.Add(offer);
                    }
                }
            }
            return offers;
        }

        public IList<Medal> GetAllMedals()
        {
            IList<Medal> medals = new List<Medal>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Medal.TABLE_NAME + "]";
                OleDbCommand command = new OleDbCommand(sql, connection);
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Medal medal = new Medal();
                        medal.Id = (Int32)reader[Medal.ColumnName.ID.ToString()];
                        medal.Name = (String)reader[Medal.ColumnName.Medal.ToString()];
                        medal.Description = (String)reader[Medal.ColumnName.Description.ToString()];
                        medals.Add(medal);
                    }
                }
            }
            return medals;
        }

        public IList<Player> GetAllPlayers()
        {
            IList<Player> playerList = new List<Player>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Player.TABLE_NAME + "]";
                OleDbCommand command = new OleDbCommand(sql, connection);
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Player player = new Player();
                        player.Cash = (Int32)reader[Player.ColumnName.Cash.ToString()];
                        player.Id = (Int32)reader[Player.ColumnName.ID.ToString()];
                        player.UserName = (String)reader[Player.ColumnName.User_name.ToString()];
                        playerList.Add(player);
                    }
                }
            }
            return playerList;
        }

        public IList<Holding> GetAllHoldings()
        {
            IList<Holding> holdings = new List<Holding>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Holding.TABLE_NAME + "]";
                OleDbCommand command = new OleDbCommand(sql, connection);
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Holding holding = new Holding();
                        holding.Id = (Int32)reader[Holding.ColumnName.ID.ToString()];
                        holding.NumPredictions = (Int32)reader[Holding.ColumnName.Num_predictions.ToString()];
                        holding.PlayerId = (Int32)reader[Holding.ColumnName.Player_id.ToString()];
                        holding.PredictionId = (Int32)reader[Holding.ColumnName.Prediction_id.ToString()];
                        holdings.Add(holding);
                    }
                }
            }
            return holdings;
        }

        public IList<Prediction> GetAllActivePredictions()
        {
            IList<Prediction> predictions = new List<Prediction>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Prediction.TABLE_NAME + "] where [" + Prediction.ColumnName.Is_active.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Prediction.ColumnName.ID.ToString(), OleDbType.Boolean).Value = true;

                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Prediction prediction = new Prediction();
                        if (reader[Prediction.ColumnName.End_date.ToString()] != DBNull.Value)
                            prediction.EndDate = (DateTime)reader[Prediction.ColumnName.End_date.ToString()];
                        prediction.HasSucceeded = (bool)reader[Prediction.ColumnName.Has_succeeded.ToString()];
                        prediction.Id = (Int32)reader[Prediction.ColumnName.ID.ToString()];
                        prediction.IsActive = (bool)reader[Prediction.ColumnName.Is_active.ToString()];
                        prediction.IsClosed = (bool)reader[Prediction.ColumnName.Is_closed.ToString()];
                        prediction.LastChange = (decimal)reader[Prediction.ColumnName.Last_change.ToString()];
                        prediction.LastValue = (Int32)reader[Prediction.ColumnName.Last_value.ToString()];
                        prediction.LongDescription = (String)reader[Prediction.ColumnName.Long_description.ToString()];
                        prediction.ShortDescription = (String)reader[Prediction.ColumnName.Short_description.ToString()];
                        prediction.Ticker = (String)reader[Prediction.ColumnName.Ticker.ToString()];
                        predictions.Add(prediction);
                    }
                }
            }
            return predictions;
        }

        public IList<Prediction> GetAllClosedPredictions()
        {
            IList<Prediction> predictions = new List<Prediction>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Prediction.TABLE_NAME + "] where [" + Prediction.ColumnName.Is_closed.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Prediction.ColumnName.ID.ToString(), OleDbType.Boolean).Value = true;

                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Prediction prediction = new Prediction(); 
                        if (reader[Prediction.ColumnName.End_date.ToString()] != DBNull.Value)
                            prediction.EndDate = (DateTime)reader[Prediction.ColumnName.End_date.ToString()];
                        prediction.HasSucceeded = (bool)reader[Prediction.ColumnName.Has_succeeded.ToString()];
                        prediction.Id = (Int32)reader[Prediction.ColumnName.ID.ToString()];
                        prediction.IsActive = (bool)reader[Prediction.ColumnName.Is_active.ToString()];
                        prediction.IsClosed = (bool)reader[Prediction.ColumnName.Is_closed.ToString()];
                        prediction.LastChange = (decimal)reader[Prediction.ColumnName.Last_change.ToString()];
                        prediction.LastValue = (Int32)reader[Prediction.ColumnName.Last_value.ToString()];
                        prediction.LongDescription = (String)reader[Prediction.ColumnName.Long_description.ToString()];
                        prediction.ShortDescription = (String)reader[Prediction.ColumnName.Short_description.ToString()];
                        prediction.Ticker = (String)reader[Prediction.ColumnName.Ticker.ToString()];
                        predictions.Add(prediction);
                    }
                }
            }
            return predictions;
        }

        public IList<PredictionResult> GetAllPredictionResults()
        {
            IList<PredictionResult> predictionResults = new List<PredictionResult>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + PredictionResult.TABLE_NAME + "]";
                OleDbCommand command = new OleDbCommand(sql, connection);

                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        PredictionResult predictionResult = new PredictionResult();
                        predictionResult.CashEarned = (Int32)reader[PredictionResult.ColumnName.Cash_earned.ToString()];
                        predictionResult.Id = (Int32)reader[PredictionResult.ColumnName.ID.ToString()];
                        predictionResult.MedalId = (Int32)reader[PredictionResult.ColumnName.Medal_id.ToString()];
                        predictionResult.NumTrades = (Int32)reader[PredictionResult.ColumnName.Num_trades.ToString()];
                        predictionResult.PlayerId = (Int32)reader[PredictionResult.ColumnName.Player_id.ToString()];
                        predictionResult.PredictionId = (Int32)reader[PredictionResult.ColumnName.Prediction_id.ToString()];
                        predictionResults.Add(predictionResult);
                    }
                }
            }
            return predictionResults;
        }

        public IList<Holding> GetHoldings(Player player)
        {
            IList<Holding> holdings = new List<Holding>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Holding.TABLE_NAME + "] where [" + Holding.ColumnName.Player_id.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Holding.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = player.Id;
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Holding holding = new Holding();
                        holding.Id = (Int32)reader[Holding.ColumnName.ID.ToString()];
                        holding.NumPredictions = (Int32)reader[Holding.ColumnName.Num_predictions.ToString()];
                        holding.PlayerId = (Int32)reader[Holding.ColumnName.Player_id.ToString()];
                        holding.PredictionId = (Int32)reader[Holding.ColumnName.Prediction_id.ToString()];
                        holdings.Add(holding);
                    }
                }
            }
            return holdings;
        }

        public IList<Holding> GetHoldings(Prediction prediction)
        {
            IList<Holding> holdings = new List<Holding>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Holding.TABLE_NAME + "] where [" + Holding.ColumnName.Prediction_id.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Holding.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = prediction.Id;
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Holding holding = new Holding();
                        holding.Id = (Int32)reader[Holding.ColumnName.ID.ToString()];
                        holding.NumPredictions = (Int32)reader[Holding.ColumnName.Num_predictions.ToString()];
                        holding.PlayerId = (Int32)reader[Holding.ColumnName.Player_id.ToString()];
                        holding.PredictionId = (Int32)reader[Holding.ColumnName.Prediction_id.ToString()];
                        holdings.Add(holding);
                    }
                }
            }
            return holdings;
        }

        public IList<Offer> GetOffers(Player player, bool includeAcceptorOffers)
        {
            IList<Offer> offers = new List<Offer>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Offer.TABLE_NAME + "] where [" + Offer.ColumnName.Player_id.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Offer.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = player.Id;

                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Offer offer = new Offer();

                        if (reader[Offer.ColumnName.Acceptor.ToString()] != DBNull.Value)
                            offer.AcceptorId = (Int32)reader[Offer.ColumnName.Acceptor.ToString()];
                        offer.EndDate = (DateTime)reader[Offer.ColumnName.End_date.ToString()];
                        offer.Id = (Int32)reader[Offer.ColumnName.ID.ToString()];
                        offer.IsAccepted = (bool)reader[Offer.ColumnName.Is_accepted.ToString()];
                        offer.IsActive = (bool)reader[Offer.ColumnName.Is_active.ToString()];
                        offer.IsBuy = (bool)reader[Offer.ColumnName.Is_buy.ToString()];
                        offer.NumPredictions = (Int32)reader[Offer.ColumnName.Num_predictions.ToString()];
                        offer.PlayerId = (Int32)reader[Offer.ColumnName.Player_id.ToString()];
                        offer.PredictionId = (Int32)reader[Offer.ColumnName.Prediction_id.ToString()];
                        offer.Price = (Int32)reader[Offer.ColumnName.Price.ToString()];
                        offers.Add(offer);
                    }
                }
            }
            if (includeAcceptorOffers)
            {
                using (OleDbConnection connection = new OleDbConnection(_connectionString))
                {
                    connection.Open();
                    string sql = "select * from [" + Offer.TABLE_NAME + "] where [" + Offer.ColumnName.Acceptor.ToString() + "] = ?";
                    OleDbCommand command = new OleDbCommand(sql, connection);
                    command.Parameters.Add(Offer.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = player.Id;

                    using (OleDbDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Offer offer = new Offer();

                            if (reader[Offer.ColumnName.Acceptor.ToString()] != DBNull.Value)
                                offer.AcceptorId = (Int32)reader[Offer.ColumnName.Acceptor.ToString()];
                            offer.EndDate = (DateTime)reader[Offer.ColumnName.End_date.ToString()];
                            offer.Id = (Int32)reader[Offer.ColumnName.ID.ToString()];
                            offer.IsAccepted = (bool)reader[Offer.ColumnName.Is_accepted.ToString()];
                            offer.IsActive = (bool)reader[Offer.ColumnName.Is_active.ToString()];
                            offer.IsBuy = (bool)reader[Offer.ColumnName.Is_buy.ToString()];
                            offer.NumPredictions = (Int32)reader[Offer.ColumnName.Num_predictions.ToString()];
                            offer.PlayerId = (Int32)reader[Offer.ColumnName.Player_id.ToString()];
                            offer.PredictionId = (Int32)reader[Offer.ColumnName.Prediction_id.ToString()];
                            offer.Price = (Int32)reader[Offer.ColumnName.Price.ToString()];
                            offers.Add(offer);
                        }
                    }
                }
            }
            return offers;
        }

        public IList<Offer> GetOffers(Prediction prediction)
        {
            IList<Offer> offers = new List<Offer>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + Offer.TABLE_NAME + "] where [" + Offer.ColumnName.Prediction_id.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Offer.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = prediction.Id;

                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Offer offer = new Offer();

                        if (reader[Offer.ColumnName.Acceptor.ToString()] !=  DBNull.Value)
                            offer.AcceptorId = (Int32)reader[Offer.ColumnName.Acceptor.ToString()];
                        offer.EndDate = (DateTime)reader[Offer.ColumnName.End_date.ToString()];
                        offer.Id = (Int32)reader[Offer.ColumnName.ID.ToString()];
                        offer.IsAccepted = (bool)reader[Offer.ColumnName.Is_accepted.ToString()];
                        offer.IsActive = (bool)reader[Offer.ColumnName.Is_active.ToString()];
                        offer.IsBuy = (bool)reader[Offer.ColumnName.Is_buy.ToString()];
                        offer.NumPredictions = (Int32)reader[Offer.ColumnName.Num_predictions.ToString()];
                        offer.PlayerId = (Int32)reader[Offer.ColumnName.Player_id.ToString()];
                        offer.PredictionId = (Int32)reader[Offer.ColumnName.Prediction_id.ToString()];
                        offer.Price = (Int32)reader[Offer.ColumnName.Price.ToString()];
                        offers.Add(offer);
                    }
                }
            }
            return offers;
        }

        public IList<PredictionResult> GetPredictionResults(Prediction prediction)
        {
            IList<PredictionResult> predictionResults = new List<PredictionResult>();
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "select * from [" + PredictionResult.TABLE_NAME + "] where [" + PredictionResult.ColumnName.Prediction_id.ToString() + "] = ?";
                sql += " order by [" + PredictionResult.ColumnName.Cash_earned.ToString() + "] DESC";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(PredictionResult.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = prediction.Id;

                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        PredictionResult predictionResult = new PredictionResult();
                        predictionResult.CashEarned = (Int32)reader[PredictionResult.ColumnName.Cash_earned.ToString()];
                        predictionResult.Id = (Int32)reader[PredictionResult.ColumnName.ID.ToString()];
                        predictionResult.MedalId = (Int32)reader[PredictionResult.ColumnName.Medal_id.ToString()];
                        predictionResult.NumTrades = (Int32)reader[PredictionResult.ColumnName.Num_trades.ToString()];
                        predictionResult.PlayerId = (Int32)reader[PredictionResult.ColumnName.Player_id.ToString()];
                        predictionResult.PredictionId = (Int32)reader[PredictionResult.ColumnName.Prediction_id.ToString()];
                        predictionResults.Add(predictionResult);
                    }
                }
            }
            return predictionResults;
        }

        #endregion

        #endregion
    }
}
